package com.visionet.school.app.web.repository.member.impl;

import com.visionet.school.app.web.common.utils.PageUtils;
import com.visionet.school.app.web.entity.member.vo.AsAndMeVo;
import com.visionet.school.app.web.entity.member.vo.AssessVo;
import com.visionet.school.app.web.entity.member.vo.MemberAssessVo;
import com.visionet.school.app.web.entity.vo.MemberVo;
import org.apache.commons.lang3.StringUtils;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.PageRequest;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import javax.annotation.Resource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * Created by hxpeng on 2017/3/15.
 */
public class MemberRepositoryImpl {

    @Resource
    private JdbcTemplate jdbcTemplate;

    public Page<MemberVo> findMemberPage(MemberVo memberVo, String sortType){
        StringBuffer sql = new StringBuffer();
        List<Object> params = new ArrayList<>();
        sql.append("select tm.id,tm.member_name,tm.member_type,tm.member_phone,tm.member_address,tm.member_birthday,tm.member_sex, tm.create_date ");
        sql.append("from t_member as tm where tm.is_deleted = '0' ");
        if(StringUtils.isNotBlank(memberVo.getMemberType())){
            sql.append("and tm.member_type = ? ");
            params.add(memberVo.getMemberType());
        }
        if(StringUtils.isNotBlank(memberVo.getMemberName())){
            sql.append("and tm.member_name like ? ");
            params.add("%"+memberVo.getMemberName()+"%");
        }
        if(StringUtils.isNotBlank(memberVo.getMemberPhone())){
            sql.append("and tm.member_phone = ? ");
            params.add(memberVo.getMemberPhone());
        }
        if(StringUtils.isNotBlank(sortType)){
            sql.append("ORDER BY tm.create_date DESC");
        }else{
            sql.append("ORDER BY tm.").append(sortType);
        }
        PageRequest page = PageUtils.getPageRequestInfo(memberVo.getPageInfo());
        if(page == null){
            page = new PageRequest(0, 10);
        }
        int currentPage = page.getPageNumber();
        int pageSize = page.getPageSize();
        int size = jdbcTemplate.queryForInt("select count(*) from ("+sql.toString()+") as c", params.toArray());
        List<MemberVo> list = jdbcTemplate.query("select t.* from ("+sql+") as t limit  "+ currentPage * pageSize+","+pageSize, params.toArray(), new RowMapper<MemberVo>() {
            @Override
            public MemberVo mapRow(ResultSet rs, int rowNum) throws SQLException {
                MemberVo entity = new MemberVo();
                entity.setId(rs.getString("id"));
                entity.setMemberName(rs.getString("member_name"));
                entity.setMemberSex(rs.getString("member_sex"));
                entity.setMemberBirthday(rs.getTimestamp("member_birthday"));
                entity.setMemberPhone(rs.getString("member_phone"));
                entity.setMemberType(rs.getString("member_type"));
                entity.setMemberAddress(rs.getString("member_address"));
                return entity;
            }
        });
        Page<MemberVo> cmProductGroupTourPage = new PageImpl<>(list, page, size);
        return cmProductGroupTourPage;
    }

    public Page<MemberVo> getStudentList(MemberVo memberVo){
        StringBuffer sql = new StringBuffer();
        List<Object> params = new ArrayList<>();
        sql.append("select tm.id,tm.member_name,tm.member_type,tm.member_phone,tm.member_address,tm.member_birthday,tm.member_sex,tc.id as memberClassId");
        sql.append(" from t_member tm,t_member_class tc where tm.id = tc.member_id and tm.is_deleted = 0 and tc.is_deleted = 0 and tm.member_type = '3'");
        if(StringUtils.isNotBlank(memberVo.getClassId())){
            sql.append(" and tc.class_id = ?");
            params.add(memberVo.getClassId());
        }
        PageRequest page = PageUtils.getPageRequestInfo(memberVo.getPageInfo());
        if(page == null){
            page = new PageRequest(0, 10);
        }
        int currentPage = page.getPageNumber();
        int pageSize = page.getPageSize();
        int size = jdbcTemplate.queryForInt("select count(*) from ("+sql.toString()+") as c", params.toArray());
        List<MemberVo> list = jdbcTemplate.query("select t.* from ("+sql+") as t limit  "+ currentPage * pageSize+","+pageSize, params.toArray(), new RowMapper<MemberVo>() {
            @Override
            public MemberVo mapRow(ResultSet rs, int rowNum) throws SQLException {
                MemberVo entity = new MemberVo();
                entity.setId(rs.getString("id"));
                entity.setMemberName(rs.getString("member_name"));
                entity.setMemberSex(rs.getString("member_sex"));
                entity.setMemberBirthday(rs.getTimestamp("member_birthday"));
                entity.setMemberPhone(rs.getString("member_phone"));
                entity.setMemberType(rs.getString("member_type"));
                entity.setMemberAddress(rs.getString("member_address"));
                entity.setMemberClassId(rs.getString("memberClassId"));
                return entity;
            }
        });
        Page<MemberVo> cmProductGroupTourPage = new PageImpl<>(list, page, size);
        return cmProductGroupTourPage;
    }

    public Page<MemberVo> queryStudentPage(MemberVo memberVo){
        StringBuffer sql = new StringBuffer();
        List<Object> params = new ArrayList<>();
        sql.append("select * from t_member as tm where tm.member_type = '3' and tm.is_deleted = '0' and tm.id not in ");
        sql.append("(select tmc.member_id from t_member_class as tmc where tmc.is_deleted = '0')");
        if(StringUtils.isNotBlank(memberVo.getMemberName())){
            sql.append(" and tm.member_name like ?");
            params.add("%"+memberVo.getMemberName()+"%");
        }
        PageRequest page = PageUtils.getPageRequestInfo(memberVo.getPageInfo());
        if(page == null){
            page = new PageRequest(0, 10);
        }
        int currentPage = page.getPageNumber();
        int pageSize = page.getPageSize();
        int size = jdbcTemplate.queryForInt("select count(*) from ("+sql.toString()+") as c", params.toArray());
        List<MemberVo> list = jdbcTemplate.query("select t.* from ("+sql+") as t limit  "+ currentPage * pageSize+","+pageSize, params.toArray(), new RowMapper<MemberVo>() {
            @Override
            public MemberVo mapRow(ResultSet rs, int rowNum) throws SQLException {
                MemberVo entity = new MemberVo();
                entity.setId(rs.getString("id"));
                entity.setMemberName(rs.getString("member_name"));
                entity.setMemberSex(rs.getString("member_sex"));
                entity.setMemberBirthday(rs.getTimestamp("member_birthday"));
                entity.setMemberPhone(rs.getString("member_phone"));
                entity.setMemberType(rs.getString("member_type"));
                entity.setMemberAddress(rs.getString("member_address"));
                return entity;
            }
        });
        Page<MemberVo> cmProductGroupTourPage = new PageImpl<>(list, page, size);
        if(cmProductGroupTourPage.getSize()>0) {
            return cmProductGroupTourPage;
        }
        return null;
    }

    public List<MemberVo> findAllfamilyInfo(String studentId){
        StringBuffer sql = new StringBuffer();
        sql.append("select member_name,rel_type from t_member m LEFT JOIN t_member_rel mr ON m.id=mr.rel_member_id " );
        sql.append(" WHERE mr.member_id = ? AND mr.is_deleted = '0'");
        List<Object> params = new ArrayList<>();
        params.add(studentId);
        List<MemberVo> list = jdbcTemplate.query(sql.toString(), params.toArray(),new RowMapper<MemberVo>() {
            MemberVo entity=new MemberVo();
            @Override
            public MemberVo mapRow(ResultSet rs, int rowNum) throws SQLException {
                MemberVo entity = new MemberVo();
                entity.setMemberRelType(rs.getString("rel_type"));
                entity.setMemberName(rs.getString("member_name"));
                return entity;
            }
        });
        return list;
    }

    public List<MemberAssessVo> getMemberList(String id) {
        StringBuffer sf = new StringBuffer();
        List<Object> param = new ArrayList<>();
        sf.append(" SELECT tm.id,tm.member_name,tm.avatar_path,tm.member_sex, ");
        sf.append(" (SELECT fa.assess_status FROM t_assess fa WHERE fa.member_id=tm.id AND fa.start_date=?)as status");
        Date d = new Date();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        param.add(sdf.format(d));
        sf.append(" FROM t_member tm LEFT JOIN t_member_class tc ON tm.id=tc.member_id ");
        sf.append(" WHERE tm.member_type='3' and tm.is_deleted='0' ");
        if (null != id && StringUtils.isNotBlank(id)) {
            sf.append(" AND tc.class_id= ? ");
            param.add(id);
        }
        sf.append(" order by status desc ");
        List<MemberAssessVo> query = jdbcTemplate.query(sf.toString(), param.toArray(), new RowMapper<MemberAssessVo>() {
            @Override
            public MemberAssessVo mapRow(ResultSet rs, int i) throws SQLException {
                MemberAssessVo vo = new MemberAssessVo();
                vo.setId(rs.getString("id"));
                vo.setMemberName(rs.getString("member_name"));
                vo.setAvatarPath(rs.getString("avatar_path"));
                vo.setMemberSex(rs.getString("member_sex"));
                vo.setAssessStatus(rs.getString("status"));
                return vo;
            }
        });
        return query;
    }

    public AsAndMeVo getAssessById(String memberId) {
        StringBuffer sf = new StringBuffer();
        List<Object> param = new ArrayList<>();
        sf.append(" SELECT a.*,b.member_name, b.member_sex, b.avatar_path,b.id as member_ids");
        sf.append(" FROM t_assess a LEFT JOIN t_member b on a.member_id=b.id ");
        sf.append( " where ");
        Date d = new Date();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        String s = sdf.format(d);
        sf.append(" a.start_date=? ");
        param.add(s);
        if (null != memberId && StringUtils.isNotBlank(memberId)) {
            sf.append(" and b.id=? ");
            param.add(memberId);
        }
        List<AsAndMeVo> vos = jdbcTemplate.query(sf.toString(), param.toArray(), new RowMapper<AsAndMeVo>() {

            @Override
            public AsAndMeVo mapRow(ResultSet rs, int i) throws SQLException {
                AsAndMeVo vo = new AsAndMeVo();
                vo.setAssessStatus(rs.getString("assess_status"));
                vo.setAvatarPath(rs.getString("avatar_path"));
                vo.setAbility(rs.getString("ability"));
                vo.setMemberName(rs.getString("member_name"));
                vo.setAtHome(rs.getString("atHome"));
                vo.setCommunication(rs.getString("communication"));
                vo.setEmotion(rs.getString("emotion"));
                vo.setHabit(rs.getString("habit"));
                vo.setHealthy(rs.getString("healthy"));
//                vo.setLanguageExpression(rs.getString("language_expression"));
                vo.setMemberSex(rs.getString("member_sex"));
                vo.setMessage(rs.getString("message"));
                vo.setOperation(rs.getString("operation"));
                vo.setSinceReason(rs.getString("since_reason"));
                vo.setSleep(rs.getString("sleep"));
                vo.setHygiene(rs.getString("hygiene"));
                vo.setTheMeal(rs.getString("the_meal"));
//                vo.setId(rs.getString("id"));
                vo.setMemberId(rs.getString("member_ids"));
                vo.setAssessId(rs.getString("id"));//由于select *，所以id是Assess的id
                return vo;
            }
        });
        if (vos.size() != 0) {
            return vos.get(0);
        }
        return null;
    }


    public int findifStatus(String classId) {
        StringBuffer sf = new StringBuffer();
        ArrayList<Object> param = new ArrayList<>();
        sf.append(" SELECT count(1) ");
        sf.append("  FROM t_assess a LEFT JOIN t_member m on a.member_id=m.id LEFT JOIN t_member_class tc on tc.member_id=m.id ");
        sf.append(" where m.member_type='3' and m.is_deleted='0' ");
        Date date = new Date();
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
        String s = format.format(date);
        sf.append(" and a.start_date=? ");
        param.add(s);
        if (null != classId && StringUtils.isNotBlank(classId)) {
            sf.append(" and tc.class_id =? ");
            param.add(classId);
        }

        int size = jdbcTemplate.queryForInt(sf.toString(), param.toArray());
        return size;
    }


    public List<AsAndMeVo> findAssess(String classId) {
        StringBuffer sf = new StringBuffer();
        ArrayList<Object> param = new ArrayList<>();
        sf.append(" SELECT m.member_name,m.avatar_path,m.member_sex,m.id ");
        sf.append(" from t_member m LEFT JOIN t_member_class tmc ON m.id=tmc.member_id ");
        sf.append(" WHERE m.is_deleted='0' and m.member_type='3' ");
        if (null != classId && StringUtils.isNotBlank(classId)) {
            sf.append(" AND tmc.class_id = ? ");
            param.add(classId);
        }
        sf.append(" and m.id NOT IN ");
        sf.append(" (SELECT tm.id FROM t_member tm ");
        sf.append(" LEFT JOIN t_assess ta ON tm.id=ta.member_id LEFT JOIN t_member_class tc on tc.member_id=tm.id ");
        sf.append(" WHERE ta.assess_status='1' and m.member_type='3' ");
        Date d = new Date();
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
        String date = format.format(d);
        sf.append(" AND ta.start_date=? ");
        param.add(date);
        if (null != classId && StringUtils.isNotBlank(classId)) {
            sf.append(" AND  tc.class_id=?) ");
            param.add(classId);
        }
        List<AsAndMeVo> query = jdbcTemplate.query(sf.toString(), param.toArray(), new RowMapper<AsAndMeVo>() {
            @Override
            public AsAndMeVo mapRow(ResultSet rs, int i) throws SQLException {
                AsAndMeVo vo = new AsAndMeVo();
                vo.setMemberName(rs.getString("member_name"));
                vo.setAvatarPath(rs.getString("avatar_path"));
                vo.setMemberSex(rs.getString("member_sex"));
                vo.setId(rs.getString("id"));
                return vo;
            }
        });
        return query;

    }


    public Map<String, Object[]> findAssessReport(String startDate, String endDate, String memberId) {
        List<Object> param = new ArrayList<>();
        StringBuffer sf = new StringBuffer();
        sf.append(" select t.* ");
        sf.append(" from t_assess t");
        sf.append(" where t.is_deleted='0' ");
        if (startDate != null && StringUtils.isNotBlank(startDate)) {
            sf.append(" and  t.start_date between ? and ?");
            param.add(startDate);
            param.add(endDate);
        }
        if (memberId != null && StringUtils.isNotBlank(memberId)) {
            sf.append(" and t.member_id = ? ");
            param.add(memberId);
        }
//        sf.append(" and t.emotion is not null ");
//        sf.append(" and t.communication is not null ");
//        sf.append(" and t.operation is not null ");
//        sf.append(" and t.habit is not null ");
//        sf.append(" and t.hygiene is not null");
//        sf.append(" and t.the_meal is not null ");
//        sf.append(" and t.since_reason is not null ");
//        sf.append(" and t.healthy is not null ");
//        sf.append(" and t.sleep is not null ");
        sf.append(" ORDER BY t.start_date ASC ");
        List<AssessVo> list = jdbcTemplate.query(sf.toString(), param.toArray(), new RowMapper<AssessVo>() {
            @Override
            public AssessVo mapRow(ResultSet rs, int i) throws SQLException {
                AssessVo vo = new AssessVo();
                vo.setEmotion(rs.getString("emotion"));
                vo.setCommunication(rs.getString("communication"));
                vo.setOperation(rs.getString("operation"));
                vo.setHabit(rs.getString("habit"));
                vo.setHygiene(rs.getString("hygiene"));
                vo.setTheMeal(rs.getString("the_meal"));
                vo.setSinceReason(rs.getString("since_reason"));
                vo.setHealthy(rs.getString("healthy"));
                vo.setSleep(rs.getString("sleep"));
                return vo;
            }
        });
        List<Integer> emotionList = new ArrayList<>();
        List<Integer> communicationList = new ArrayList<>();
        List<Integer> operationList = new ArrayList<>();
        List<Integer> habitList = new ArrayList<>();
        List<Integer> hygieneList = new ArrayList<>();
        List<Integer> theMealList = new ArrayList<>();
        List<Integer> sinceReasonList = new ArrayList<>();
        List<Integer> healthyList = new ArrayList<>();
        List<Integer> sleepList = new ArrayList<>();
        Map<String, Object[]> map = new HashMap<>();
        for (AssessVo data: list) {
            emotionList.add(Integer.parseInt(data.getEmotion()));
            communicationList.add(Integer.parseInt(data.getCommunication()));
            operationList.add(Integer.parseInt(data.getOperation()));
            habitList.add(Integer.parseInt(data.getHabit()));
            hygieneList.add(Integer.parseInt(data.getHygiene()));
            theMealList.add(Integer.parseInt(data.getTheMeal()));
            sinceReasonList.add(Integer.parseInt(data.getSinceReason()));
            healthyList.add(Integer.parseInt(data.getHealthy()));
            sleepList.add(Integer.parseInt(data.getSleep()));
        }
        map.put("emotion",emotionList.toArray());
        map.put("communication",communicationList.toArray());
        map.put("operation", operationList.toArray());
        map.put("habit",habitList.toArray());
        map.put("hygiene", hygieneList.toArray());
        map.put("theMeal", theMealList.toArray());
        map.put("sinceReason", sinceReasonList.toArray());
        map.put("healthy", healthyList.toArray());
        map.put("sleep",sleepList.toArray());
        map.put("sumAssess",list.toArray());
        return map;
    }

    public List<AssessVo> findAssessReportFraction(String startDate, String endDate, String memberId) {
        List<Object> param = new ArrayList<>();
        StringBuffer sf = new StringBuffer();
        sf.append(" select sum(t.emotion) as sumEmotion,sum(t.communication) as sumCommunication,sum(t.operation) as sumOperation, ");
        sf.append(" sum(t.habit) as sumHabit, sum(t.hygiene) as sumHygiene, sum(t.the_meal) as sumTheMeal,sum(t.since_reason) as sumSinceReason, ");
        sf.append(" sum(t.healthy) as sumHealthy, sum(t.sleep) as sumSleep ");
        sf.append(" from t_assess t");
        sf.append(" where t.is_deleted='0' ");
        if (startDate != null && StringUtils.isNotBlank(startDate)) {
            sf.append(" and  t.start_date between ? and ?");
            param.add(startDate);
            param.add(endDate);
        }
        if (memberId != null && StringUtils.isNotBlank(memberId)) {
            sf.append(" and t.member_id = ? ");
            param.add(memberId);
        }
        sf.append(" and t.emotion is not null ");
        sf.append(" and t.communication is not null ");
        sf.append(" and t.operation is not null ");
        sf.append(" and t.habit is not null ");
        sf.append(" and t.hygiene is not null");
        sf.append(" and t.the_meal is not null ");
        sf.append(" and t.since_reason is not null ");
        sf.append(" and t.healthy is not null ");
        sf.append(" and t.sleep is not null ");
        sf.append(" ORDER BY t.start_date ASC ");
        List<AssessVo> list = jdbcTemplate.query(sf.toString(), param.toArray(), new RowMapper<AssessVo>() {
            @Override
            public AssessVo mapRow(ResultSet rs, int i) throws SQLException {
                AssessVo vo = new AssessVo();
                vo.setSumEmotion(rs.getInt("sumEmotion"));
                vo.setSumCommunication(rs.getInt("sumCommunication"));
                vo.setSumHabit(rs.getInt("sumHabit"));
                vo.setSumHealthy(rs.getInt("sumHealthy"));
                vo.setSumHygiene(rs.getInt("sumHygiene"));
                vo.setSumOperation(rs.getInt("sumOperation"));
                vo.setSumSinceReason(rs.getInt("sumSinceReason"));
                vo.setSumSleep(rs.getInt("sumSleep"));
                vo.setSumTheMeal(rs.getInt("sumTheMeal"));
                return vo;
            }
        });
        return list;
    }
    public int findAssessReportFractionSize(String startDate, String endDate, String memberId) {
        List<Object> param = new ArrayList<>();
        StringBuffer sf = new StringBuffer();
        sf.append(" select sum(t.emotion) as sumEmotion,sum(t.communication) as sumCommunication,sum(t.operation) as sumOperation, ");
        sf.append(" sum(t.habit) as sumHabit, sum(t.hygiene) as sumHygiene, sum(t.the_meal) as sumTheMeal,sum(t.since_reason) as sumSinceReason, ");
        sf.append(" sum(t.healthy) as sumHealthy, sum(t.sleep) as sumSleep ");
        sf.append(" from t_assess t");
        sf.append(" where t.is_deleted='0' ");
        if (startDate != null && StringUtils.isNotBlank(startDate)) {
            sf.append(" and  t.start_date between ? and ?");
            param.add(startDate);
            param.add(endDate);
        }
        if (memberId != null && StringUtils.isNotBlank(memberId)) {
            sf.append(" and t.member_id = ? ");
            param.add(memberId);
        }
        sf.append(" and t.emotion is not null ");
        sf.append(" and t.communication is not null ");
        sf.append(" and t.operation is not null ");
        sf.append(" and t.habit is not null ");
        sf.append(" and t.hygiene is not null");
        sf.append(" and t.the_meal is not null ");
        sf.append(" and t.since_reason is not null ");
        sf.append(" and t.healthy is not null ");
        sf.append(" and t.sleep is not null ");
        sf.append(" ORDER BY t.start_date ASC ");

        int size = jdbcTemplate.queryForInt("SELECT count(1) FROM (" + sf.toString() + ") as C ", param.toArray());
        return size;
    }


}
